/**
 * This class can be used to generically download data from any database
 * by executing any SQL.
 * Currently, it support Oracle and MySql databases, however adding support
 * for other databases is trivial.
 * A configuration file GenerateCSV.properties defines all the queries,
 * their database details and output file name etc.
 * This file should be kept in the same directory as the java class.
 * Also, ensure that all the database driver classes (jars) are in the classpath.
 * 
 * Most of the methods do not handle exceptions, they just keep propogating it
 * In case of exceptions, the user will be notified on the console.
 */

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Properties;
import org.apache.commons.lang.StringEscapeUtils;

/* @author rjain
 */

public class GenerateCSV
{
	public static final String PROPERTY_FILE = "GenerateCSV.properties";
	
	public static final String ORACLE_DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
	
	public static final String MYSQL_DRIVER_CLASS = "com.mysql.jdbc.Driver";

	public GenerateCSV()
	{
	}
	
	/**
	 * initialize()
	 * Loads all database driver classes
	 * To add support for other databases - add their classes to this method
	 */
	public void initialize() throws ClassNotFoundException
	{
		// Load the Database driver classes

		System.out.println("Loading the Database driver classes.\n");
		Class.forName(ORACLE_DRIVER_CLASS);
		Class.forName(MYSQL_DRIVER_CLASS);
	}

	/**
	 * generateReport()
	 * Generates report by executing the input SQL on the given DB/username/password
	 * the SQL query output is written to the input reportFile
	 * The first row is a header row with the column names
	 * Remaining rows contain the data returned by SQL
	 */
	private void generateReport(String databaseURL, String username, String password,
			String sql, String reportFile) throws Exception
	{
		System.out.println("Opening a connection for " + databaseURL + "\n");
		Connection conn = DriverManager.getConnection(databaseURL, username, password);
		
		if(conn == null)
			throw new Exception("Could not get a connection to " + databaseURL);
		
		Statement stmt = conn.createStatement();
		
		System.out.println("Executing query: " + sql);
		ResultSet rs = stmt.executeQuery(sql);
		
		// Get the meta data for the result set
		ResultSetMetaData rsmd = rs.getMetaData();
		int numCols = rsmd.getColumnCount();

		// Open the output stream
		PrintWriter out = new PrintWriter(new FileOutputStream(reportFile, false));

		// Print the header row
		System.out.println("Printing the header row, total columns: " + numCols);
		for (int i = 1; i <= numCols; i++)
		{
			// Print the column name followed by a tab
			out.print(rsmd.getColumnName(i));
			if(i < numCols)
				out.print(",");
		}
		out.println();
		
		// Print the data
		System.out.println("Printing the data rows");
		int totalRows = 0;
		while (rs.next())
		{
			for(int i = 1; i <= numCols; i++)
			{
				// Print the column value followed by a tab
				Object o = rs.getObject(i);
				if(o instanceof java.lang.String)
					out.print(StringEscapeUtils.escapeCsv((String)o));
				else
					out.print(rs.getObject(i));
				if(i < numCols)
					out.print(",");
			}
			out.println();
			totalRows++;
		}
		
		System.out.println("Total number of rows printed: " + totalRows);
		
		// Close all handles
		stmt.close();
		rs.close();
		out.close();
	}
	
	/**
	 * main()
	 * No arguments are expected by the main method.
	 * It opens the property file (GenerateCSV.properties) and reads the entries from there
	 * All output file paths are assumed to be from the current directory
	 */
	
	public static void main (String[] args) throws Exception
	{
		// Instantiate and initialize the csvGenerator object
		GenerateCSV csvGenerator = new GenerateCSV();
		csvGenerator.initialize();

		// Load data from the properties file
		FileInputStream input = new FileInputStream(PROPERTY_FILE);
		Properties params = new Properties();
		params.load(input);

		// Retrieve report details for report.1 to report.n, and process them
		int count = 1;
		while(true)
		{
			String reportName = params.getProperty("report." + count + ".name");
			if(isEmpty(reportName))
			{
				// There's no report name defined - done printing all reports
				// If the user missed defining a report in the sequence then tough luck!
				System.out.println("Total reports printed: " + (count - 1));
				System.out.println("Done.");
				System.exit(0);
			}
			
			// Get all report parameters for this report
			String databaseURL = params.getProperty("report." + count + ".db");
			String username = params.getProperty("report." + count + ".username");
			String password = params.getProperty("report." + count + ".password");
			String sql = params.getProperty("report." + count + ".sql");
			String reportFile = params.getProperty("report." + count + ".file");				
			
			if(isEmpty(databaseURL) || isEmpty(username) || isEmpty(password) || isEmpty(sql) || isEmpty(reportFile))
			{
				// All parameters are mandatory
				System.out.println("Please define all the parameters [db, username, password, sql, file]" +
						" for the report " + reportName + "(report." + count + ")");
			}
			else
			{
				// Generate the report for the given data
				System.out.println("Generating report " + reportName + " (report." + count + ")");
				csvGenerator.generateReport(databaseURL, username, password, sql, reportFile);
				System.out.println();
			}

			count++;
		}
	}

	/**
	 * isEmpty()
	 * check if the input string is either null or is 0 length or only consists of spaces
	 */
	private static boolean isEmpty(String str)
	{
		return (str == null || str.trim().length() == 0);
	}
}
